package my.xh.excel.core;


import my.convert.Map2Bean;
import my.xh.excel.annotation.CellConf;
import my.xh.excel.annotation.CellFont;
import my.xh.excel.enums.CountTypeEnum;
import my.xh.excel.enums.ExtNameEnum;
import my.xh.excel.exception.ExcelUtilException;
import my.xh.excel.pojo.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Auther: xh
 * @Date: 2019/7/27 22:36
 * @Description: excel 导入导出工具 主类
 * 主标题可选，副标题栏必有
 */
public class ExcelUtil<T> {
    Logger logger = LoggerFactory.getLogger(getClass());
    private ExcelConfig excelConfig = null;

    public ExcelConfig getExcelConfig() {
        return excelConfig;
    }

    /**
     * @return java.util.List<T>
     * @Author xh
     * @Description 导入excel功能，将excel转成List数据集
     * @Date 16:05 2019/8/2
     * @Param [inputStream 输入流
     * , templateModel 导入（或导出）模块。
     * ]
     **/
    public List<T> importExcel(InputStream inputStream, TemplateModel<T> templateModel) {
        if (templateModel == null) throw new ExcelUtilException("templateModel 不能为空");

        List<T> list = new ArrayList<T>();
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            int numberOfSheets = workbook.getNumberOfSheets();
            //遍历表s
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (getExcelConfig() == null) excelConfig = parseTemplateModel(templateModel, workbook);
                ExcelDataArea dataArea = excelConfig.getDataArea();
                dataArea.getStartRow();
                int lastRowNum = sheet.getLastRowNum();
                Map<String, Object> map = new HashMap<>();
                //遍历数据区的行s
                for (int j = dataArea.getStartRow(); j <= lastRowNum; j++) {
                    Row row = sheet.getRow(j);
                    map.clear();
                    //遍历列s
                    for (ExcelDataColumn column : dataArea.getDataColumns()) {
                        Cell cell = row.getCell(column.getCol());
                        if (cell != null) {
                            Object o = getValue(cell);//值
                            if (o != null && StringUtils.isNotBlank(o.toString())) map.put(column.getPropertyName(), o);
                        } else {
                            logger.debug("{} , {} , {}单元格为空",sheet.getSheetName(), j, column.getCol());
                        }

                    }
                    T bean = Map2Bean.getInstance().getBean(templateModel.getSupportClass(), map, null);
                    list.add(bean);
                }
            }
        } catch (Exception e) {
            logger.error("导入excel异常，{}", e.toString());
        }
        return list;
    }

    //获取单元格的值
    private Object getValue(Cell cell) {
        Object o = null;
        try {
            o = cell.getStringCellValue();
            if (o == null) return null;
        } catch (Exception e) {
            try {
                o = cell.getNumericCellValue();
                //整数的处理，如 12.0 去掉.0
                if (o != null && o.toString().endsWith(".0")) o = ((Double) o).intValue();
            } catch (Exception e1) {
                try {
                    o = cell.getDateCellValue();
                } catch (Exception e2) {
                    try {
                        o = cell.getBooleanCellValue();
                    } catch (Exception e3) {
                        logger.error("导入excel，获取单元格数据时，发生异常，{}", e.toString());
                    }
                }
            }
        }
        return o;
    }

    /**
     * @return org.apache.poi.ss.usermodel.Workbook
     * @Author xh
     * @Description 导出excel文档
     * @Date 7:52 2019/8/2
     * @Param [sources 导出数据源，为List集合
     * , templateModel 导出配置模板
     * , outExtName 输出文件格式，枚举值
     * , workbook 工作薄（当需要在一个工作薄，创建不同表的时候，可以将原工作薄传进来），可为null
     * , sheetName 表名 （当需要自定义表名时传），可为null
     * ]
     **/
    public Workbook export(List<T> sources, TemplateModel<T> templateModel, ExtNameEnum outExtName, Workbook workbook, String sheetName) {
        if (templateModel == null) throw new ExcelUtilException("templateModel 不能为空");
        if (sources == null || sources.size() == 0) throw new ExcelUtilException("数据源为空,无法完成数据导出");
        if (!templateModel.isSupport(sources.get(0).getClass())) throw new ExcelUtilException("TemplateModel类型不匹配");

        //创建工作薄
        workbook = createWorkbook(outExtName, workbook);
        //解析templateModel基本配置
        if (getExcelConfig() == null) excelConfig = parseTemplateModel(templateModel, workbook);
        //解析样式模版文件
        // parseStyleFile(excelConfig, templateModel, outExtName,workbook);
        //创建工作表
        Sheet sheet = createSheet(workbook, sheetName);
        //数据区最大列号，合并标题单元格，和生成统计类型标题时要用到
        int maxCol = getMaxCol(excelConfig);
        //成表表格的主控行号
        int row = 0;
        //1.主标题
        row = createMainTitle(excelConfig, sheet, maxCol, row);
        //2.副标题
        row = createSubTitle(excelConfig, sheet, row);
        //3.数据区
        try {
            createDataArea(sources, workbook, excelConfig, sheet, maxCol, row);
        } catch (Exception e) {
            logger.error("excel导出，数据区异常：{}", e.toString());
        }
        if (excelConfig.isAutoSizeColumn()) for (int i = 0; i <= maxCol; i++) {
            sheet.autoSizeColumn(i);
        }

        return workbook;
    }

    //创建工作薄
    private Workbook createWorkbook(ExtNameEnum outExtName, Workbook workbook) {
        if (workbook == null) {
            if (outExtName.equals(ExtNameEnum.XLSX)) {
                workbook = new XSSFWorkbook();
            } else if (outExtName.equals(ExtNameEnum.XLS)) {
                workbook = new HSSFWorkbook();
            }
        }
        return workbook;
    }

    //生成数据区
    private void createDataArea(List<T> sources, Workbook workbook, ExcelConfig excelConfig, Sheet sheet, int maxCol, int row) throws NoSuchFieldException, IllegalAccessException {
        ExcelDataArea dataArea = excelConfig.getDataArea();
        Class<?> tClass = null;
        if (sources != null && sources.size() > 0) {
            tClass = sources.get(0).getClass();
        }
        if (dataArea.getDataColumns().size() > 0) {
            Map<String, CellStyle> cellStyleMap = new HashMap<String, CellStyle>();
            // 隔行变色处理
            if (dataArea.isInterlacedDiscoloration()) for (int i = 0; i < dataArea.getDataColumns().size(); i++) {
                cellStyleMap.put("cellStyle" + dataArea.getDataColumns().get(i).getCol(), workbook.createCellStyle());
            }
            CellStyle cellStyle = null;

            for (T bean : sources) {
                row++;
                Row row1 = sheet.createRow(row);
                for (ExcelDataColumn dataColumn : dataArea.getDataColumns()) {
                    Cell cell = row1.createCell(dataColumn.getCol());
                    //隔行变色
                    if (dataArea.isInterlacedDiscoloration() && dataColumn.getCellStyle().getFillForegroundColor() == 9 && row % 2 == 1) {
                        cellStyle = cellStyleMap.get("cellStyle" + dataColumn.getCol());
                        cellStyle.cloneStyleFrom(dataColumn.getCellStyle());
                        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
                        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        cell.setCellStyle(cellStyle);
                    } else {
                        cell.setCellStyle(dataColumn.getCellStyle());
                    }

                    Field field = tClass.getDeclaredField(dataColumn.getPropertyName());
                    field.setAccessible(true);
                    Object o = field.get(bean);
                    if (o != null) {
                        setValue(dataColumn, cell, o);//赋值
                    }
                }
            }
            int dataEndRow = row;
            //数据区-统计
            row = createDataAreaOfCount(excelConfig, sheet, maxCol, row, dataArea, dataEndRow);
        }
    }

    //创建数据-统计区
    private int createDataAreaOfCount(ExcelConfig excelConfig, Sheet sheet, int maxCol, int row, ExcelDataArea dataArea, int dataEndRow) {
        Map<CountTypeEnum, Row> countTypeEnumRowMap = new HashMap<>();
        for (ExcelDataColumn dataColumn : dataArea.getDataColumns()) {
            if (dataColumn.getCountTypes().size() > 0) for (CountTypeEnum ct : dataColumn.getCountTypes()) {
                Row row1 = countTypeEnumRowMap.get(ct);
                if (row1 == null) {
                    row++;
                    row1 = sheet.createRow(row);
                    countTypeEnumRowMap.put(ct, row1);
                }
                Cell cell = row1.createCell(dataColumn.getCol());
                String colString = CellReference.convertNumToColString(dataColumn.getCol());  //长度转成ABC列
                String countstring = ct.getExcelFunction() + "(" + colString + "2:" + colString + dataEndRow + ")";//统计公式
                cell.setCellFormula(countstring);

                Cell cell1 = row1.getCell(dataArea.getDataColumns().size());
                if (cell1 == null) {
                    cell1 = row1.createCell(maxCol + 1);
                    cell1.setCellValue(ct.getLable());
                }
            }
        }
        //添加统计类型说明列 标题
        if (countTypeEnumRowMap.size() > 0) {
            Cell cell = sheet.getRow(excelConfig.getSubTitle().getRow())
                    .createCell(maxCol + 1);
            cell.setCellValue("统计类型");
        }
        return row;
    }

    //赋值
    private void setValue(ExcelDataColumn dataColumn, Cell cell, Object o) {
        //如果配有值转换器，进行值转换
        List<ValueConverter> valueConverters = dataColumn.getValueConverters();
        if (valueConverters.size() > 0) {
            for (ValueConverter v : valueConverters) {
                o = v.converter(o);
            }
        }
        //字符串或枚举或布尔，输出toString()
        boolean toString = CharSequence.class.isAssignableFrom(o.getClass())
                || Boolean.class.isAssignableFrom(o.getClass())
                || boolean.class.isAssignableFrom(o.getClass())
                || o.getClass().isEnum();
        if (toString) {
            cell.setCellValue(o.toString());
            return;
        }
        //数值型 输出数值
        if (Number.class.isAssignableFrom(o.getClass())) {
            cell.setCellValue(((Number) o).doubleValue());
            return;
        }
        if (Date.class.isAssignableFrom(o.getClass())) {
            SimpleDateFormat sdf = new SimpleDateFormat(dataColumn.getDateFormat());
            cell.setCellValue(sdf.format((Date) o));
        }
    }

    //生成副标题栏
    private int createSubTitle(ExcelConfig excelConfig, Sheet sheet, int row) {
        ExcelSubTitle subTitle = excelConfig.getSubTitle();
        if (subTitle.getColumnTitleList().size() > 0) {
            row = subTitle.getRow();
            Row row1 = sheet.createRow(row);
            for (ExcelColumnTitle columnTitle : subTitle.getColumnTitleList()) {
                Cell cell = row1.createCell(columnTitle.getCol());
                cell.setCellStyle(columnTitle.getCellStyle());
                cell.setCellValue(columnTitle.getColumnName());
                if (columnTitle.getColumnWidth() > 0)
                    sheet.setColumnWidth(columnTitle.getCol(), (int) ((columnTitle.getColumnWidth() + 0.72) * 256));
            }
        }
        return row;
    }

    //生成主标题
    private int createMainTitle(ExcelConfig excelConfig, Sheet sheet, int maxCol, int row) {
        ExcelMainTitle mainTitle = excelConfig.getMainTitle();
        if (StringUtils.isNotBlank(mainTitle.getMainTitleContent())) {
            row = mainTitle.getRow();
            Row row1 = sheet.createRow(row);
            Cell cell = row1.createCell(0);
            //合并单元格
            if (excelConfig.getSubTitle().getColumnTitleList().size() > 0) {
                CellRangeAddress region = new CellRangeAddress(row, row, 0, maxCol);
                sheet.addMergedRegion(region);
            }

            cell.setCellStyle(mainTitle.getCellStyle());

            cell.setCellValue(mainTitle.getMainTitleContent());
        }
        return row;
    }

    //获取数据列最大列号
    private int getMaxCol(ExcelConfig excelConfig) {
        int maxCol = 0;
        if (excelConfig.getSubTitle().getColumnTitleList().size() > 0) {
            for (ExcelColumnTitle e : excelConfig.getSubTitle().getColumnTitleList()) {
                if (maxCol < e.getCol()) maxCol = e.getCol();
            }
        }
        return maxCol;
    }

    //创建表
    private Sheet createSheet(Workbook workbook, String sheetName) {
        Sheet sheet;
        if (StringUtils.isNotBlank(sheetName)) {
            sheet = workbook.createSheet(sheetName);
        } else {
            sheet = workbook.createSheet();
        }
        return sheet;
    }

    //解析数据模板
    private ExcelConfig parseTemplateModel(TemplateModel<T> templateModel, Workbook workbook) {
        ExcelConfig excelConfig = new ExcelConfig();
        try {
            Class<? extends TemplateModel> modelClazz = templateModel.getClass();

            Field[] declaredFields = modelClazz.getDeclaredFields();
            Field[] supperFields = modelClazz.getSuperclass().getDeclaredFields();
            ArrayList<Field> fields = new ArrayList<Field>();
            fields.addAll(Arrays.asList(supperFields));
            fields.addAll(Arrays.asList(declaredFields));

            ExcelMainTitle mainTitle = new ExcelMainTitle();
            excelConfig.setMainTitle(mainTitle);

            ExcelSubTitle subTitle = new ExcelSubTitle();
            excelConfig.setSubTitle(subTitle);
            List<ExcelColumnTitle> columnTitleList = subTitle.getColumnTitleList();

            ExcelDataArea dataArea = new ExcelDataArea();
            excelConfig.setDataArea(dataArea);
            List<ExcelDataColumn> dataColumns = dataArea.getDataColumns();

            for (Field field : fields) {
                field.setAccessible(true);
                //主标题
                if (StringUtils.equals(field.getName(), "MAIN_TITLE")) {

                    CellConf cell = field.getAnnotation(CellConf.class);
                    if (cell != null) {
                        if (StringUtils.isNotBlank(cell.value())) mainTitle.setMainTitleContent(cell.value());
                        Object o = field.get(templateModel);
                        if (o != null) mainTitle.setMainTitleContent(o.toString());
                        //字体
                        CellFont cellFont = cell.font();
                        Font font = createFont(workbook, cellFont);
                        //样式
                        CellStyle cellStyle = createCellStyle(workbook, cell, font);
                        mainTitle.setCellStyle(cellStyle);
                    }
                    mainTitle.setRow(cell.index());
                } else if (StringUtils.equals(field.getName(), "EXPORT_FILE_NAME")) {

                    Object o = field.get(templateModel);
                    String exportFileName = templateModel.getClass().getSimpleName();
                    if (o != null && StringUtils.isNotBlank(o.toString())) {
                        exportFileName = o.toString();
                    }
                    excelConfig.setExportFileName(exportFileName);
                } else if (StringUtils.equals(field.getName(), "IS_INTERLACED_DISCOLORATION")) {

                    Object o = field.get(templateModel);
                    if (o != null) {
                        excelConfig.getDataArea().setInterlacedDiscoloration(Boolean.valueOf(o.toString()));
                    }
                } else if (StringUtils.equals(field.getName(), "IS_AUTO_SIZE_COLUMN")) {

                    Object o = field.get(templateModel);
                    if (o != null) {
                        excelConfig.setAutoSizeColumn(Boolean.valueOf(o.toString()));
                    }
                } else {

                    CellConf cell = field.getAnnotation(CellConf.class);
                    if (cell != null) {
                        //副标题
                        ExcelColumnTitle excelColumnTitle = new ExcelColumnTitle();
                        columnTitleList.add(excelColumnTitle);
                        if (StringUtils.isNotBlank(cell.value())) {
                            excelColumnTitle.setColumnName(cell.value());
                        } else {
                            excelColumnTitle.setColumnName(field.getName());
                        }
                        excelColumnTitle.setCol(cell.index());

                        CellFont cellFont = cell.font();
                        //创建字体
                        Font font = createFont(workbook, cellFont);
                        //创建样式
                        CellStyle cellStyle = createCellStyle(workbook, cell, font);
                        excelColumnTitle.setCellStyle(cellStyle);
                        //列宽
                        excelColumnTitle.setColumnWidth(cell.columnWidth());
                        //数据区
                        ExcelDataColumn excelDataColumn = new ExcelDataColumn();
                        dataColumns.add(excelDataColumn);
                        excelDataColumn.setCol(cell.index());
                        excelDataColumn.setPropertyName(field.getName());
                        excelDataColumn.setCellStyle(cellStyle);
                        excelDataColumn.setColumnWidth(cell.columnWidth());
                        excelDataColumn.setDateFormat(cell.dateFormat());
                        //数据区-保留小数位数
                        cellStyle.setDataFormat((short) cell.keepDecimalDigits());
                        //数据区-值转换器
                        Class<? extends ValueConverter>[] classes = cell.valueConverterClasses();
                        if (classes.length > 0) for (int i = 0; i < classes.length; i++) {
                            if (classes[i] != null) {
                                ValueConverter valueConverter = classes[i].newInstance();
                                excelDataColumn.getValueConverters().add(valueConverter);
                            }
                        }
                        //数据区-统计类型s
                        CountTypeEnum[] countTypeEnums = cell.countTypes();
                        if (countTypeEnums.length > 0) for (int i = 0; i < countTypeEnums.length; i++) {
                            excelDataColumn.getCountTypes().add(countTypeEnums[i]);
                        }
                    }

                }
            }
            //号行处理
            rowHandle(excelConfig);
        } catch (Exception e) {
            logger.error("parseTemplateModel 方法异常：{}", e.toString());
        }
        return excelConfig;
    }

    private void rowHandle(ExcelConfig excelConfig) {
        int row = 0;
        //如果主标题不为空，副标题所在行加1
        if (StringUtils.isNotBlank(excelConfig.getMainTitle().getMainTitleContent())) {
            row = excelConfig.getMainTitle().getRow() + 1;
        }
        //副标题
        if (excelConfig.getSubTitle().getRow() == 0) excelConfig.getSubTitle().setRow(row);
        row++;
        //数据区起始行
        excelConfig.getDataArea().setStartRow(row);
    }

    private CellStyle createCellStyle(Workbook workbook, CellConf cell, Font font) {
        CellStyle cellStyle = workbook.createCellStyle();
        //填充颜色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(cell.fillForegroundColor());
        cellStyle.setBorderBottom(BorderStyle.THIN);//设置下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//设置左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//设置右边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.index);
        cellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.index);
        cellStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.index);
        cellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.index);
        //对齐方式
        cellStyle.setAlignment(HorizontalAlignment.CENTER.valueOf(cell.alignment()));
        //自动换行
        cellStyle.setWrapText(cell.wrapText());
        cellStyle.setFont(font);
        //日期格式 测试过没有效果，因而注释
//        CreationHelper creationHelper = workbook.getCreationHelper();
//        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy年m月d日"));
        return cellStyle;
    }

    private Font createFont(Workbook workbook, CellFont cellFont) {
        Font font = workbook.createFont();
        font.setColor(cellFont.color());
        font.setFontHeightInPoints(cellFont.height());
        font.setFontName(cellFont.name());
        font.setItalic(cellFont.isItalic());
        font.setBold(cellFont.isBold());
        return font;
    }
//本来想从excel模块文件中读取样式，这样的话，样式定义可能更丰富，但后来实现过程中发现有困难，加入导出数据功能可能也不需要过多花哨的样式，于是放弃了它的实现
//    private void parseStyleFile(ExcelConfig excelConfig, TemplateModel<T> templateModel, ExtNameEnum outExtName,Workbook workbook) {
//        //在类路径下查找跟templateModel同名的xls或xlsx样式模版文件
//        String simpleName = templateModel.getClass().getSimpleName();
//        InputStream resourceAsStream = templateModel.getClass().getResourceAsStream(simpleName + "." + outExtName.getExtName());
//        if (resourceAsStream != null) {
//            try {
//                Workbook wb = WorkbookFactory.create(resourceAsStream);
//                Sheet sheet = wb.getSheetAt(0);
//                int lastRowNum = sheet.getLastRowNum();
//
//                //主标题样式
//                Row row = sheet.getRow(excelConfig.getMainTitle().getRow());
//                short lastCellNum = row.getLastCellNum();
//                for (int i = 0; i < lastCellNum; i++) {
//                    Cell cell = row.getCell(i);
//                    CellStyle cellStyle = cell.getCellStyle();
//                    excelConfig.getMainTitle().getCellStyle().cloneStyleFrom(cellStyle);
//
//                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
//                    if(i>0)break;
//                }
//                //副标题样式
//                row = sheet.getRow(excelConfig.getSubTitle().getRow());
//                for (int i = 0; i < row.getLastCellNum(); i++) {
//                    Cell cell = row.getCell(i);
//                    CellStyle cellStyle = cell.getCellStyle();
//                    List<ExcelColumnTitle> columnTitleList = excelConfig.getSubTitle().getColumnTitleList();
//                    for (ExcelColumnTitle title : columnTitleList) {
//                        if (Integer.valueOf(title.getCol()).equals(Integer.valueOf(i))) {
//                            title.getCellStyle().cloneStyleFrom(cellStyle);
//
//                           break;
//                        }
//                    }
//
//                }
//                //数据区样式
//                row = sheet.getRow(excelConfig.getDataArea().getStartRow());
//                for (int i = 0; i < row.getLastCellNum(); i++) {
//                    Cell cell = row.getCell(i);
//                    CellStyle cellStyle = cell.getCellStyle();
//                    List<ExcelDataColumn> dataColumns = excelConfig.getDataArea().getDataColumns();
//                    for (ExcelDataColumn column : dataColumns) {
//                        if (Integer.valueOf(column.getCol()).equals(Integer.valueOf(i))) {
//                            column.getCellStyle().cloneStyleFrom(cellStyle);
//
//                            break;
//                        }
//                    }
//
//                }
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//        }
//
//    }
}
